package com.wit.witmg.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.wit.witmg.entity.TEDdata;
import com.wit.witmg.entity.TJldzpcom;
import com.wit.witmg.entity.TTjdzpcom;
import com.wit.witmg.entity.TTool;
import com.wit.witmg.mapper.TJldzpcomMapper;
import com.wit.witmg.mapper.TTjdzpcomMapper;
import com.wit.witmg.service.*;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.wit.witmg.util.ErrorCode;
import com.wit.witmg.util.JsonResult;
import com.wit.witmg.util.StringUtils;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  服务实现类
 * </p>
 *
 * @author bob
 * @since 2025-03-26
 */
@Service
@RequiredArgsConstructor
public class TJldzpcomServiceImpl extends ServiceImpl<TJldzpcomMapper, TJldzpcom> implements ITJldzpcomService {
    final ITECdataService tecdataService;
    final ITEDdataService teddataService;
    final ITEAdataService teadataService;
    final TTjdzpcomMapper tjMapper;
    final ITefdataService tefdataService;

    @Override
    public Object add(HttpServletRequest request, String json) {
//        json="{\"guid\":10001,\"ph\":\"CS_2508061_3\",\"bn\":\"\",\"tp\":\"4\",\"remark\":\"\",\"date\":\"2025-09-17 12:00:00\",\"date1\":\"2025-09-17 16:00:00\",\"tec\":\"[[25,157,207,237,259,279,297,315,331,347,362,397,457,557,676,793,827,827,785,665,450,250,100],[\\\"36.4\\\",\\\"147\\\",\\\"198\\\",\\\"227\\\",\\\"251\\\",\\\"271\\\",\\\"288\\\",\\\"307\\\",\\\"322\\\",\\\"347\\\",\\\"352\\\",\\\"424\\\",\\\"478\\\",\\\"564\\\",\\\"667\\\",\\\"780\\\",\\\"814\\\",\\\"814\\\",\\\"802\\\",\\\"676\\\",0,0,0],[\\\"36.4\\\",\\\"166\\\",\\\"213\\\",\\\"245\\\",\\\"267\\\",\\\"286\\\",\\\"305\\\",\\\"323\\\",\\\"338\\\",\\\"355\\\",\\\"370\\\",\\\"405\\\",\\\"465\\\",\\\"565\\\",\\\"685\\\",\\\"800\\\",\\\"834\\\",\\\"834\\\",\\\"780\\\",\\\"669\\\",0,0,0],0]\",\"xh\":\"1\",\"hege\":\"0\"}";
        TJldzpcom t= JSON.parseObject(json,TJldzpcom.class);
        if(t.getId()>0){
            boolean bl=  updateById(t);
            if(!bl){
                return JsonResult.error(ErrorCode.ERR_200);
            }
        }else{
            boolean bl= save(t);
            if(!bl){
                return JsonResult.error(ErrorCode.ERR_504);
            }
        }
        return JsonResult.success(t);
    }

    @Override
    public Object get(HttpServletRequest request,Integer tmd, String ph, String xh,String dt0,String dt1) {
//        你根据tmd判断，12，方波 13：残压tecdata/get；14：直流1mA"teddata/get"；15：老化 "teadata/get"
        if(tmd==13){
            return tecdataService.get(request,ph,xh,false);
        }else if(tmd==14){
            return teddataService.get(request,ph,xh,false);
        }else if(tmd==15){
            return teadataService.get(request,ph);
        }
       //查询工序检验记录，参数批号ph和序号xh，xh不为空时，根据ph和xh返回单条数据；xh为空时，根据ph返回对应所有测试结果数据v系列的平均值和标准差值以及批号xh、bn、tp、开始时间time0、结束时间time1、总数qt、合格数qqt。
        if(ph!=null && xh!=null){
            QueryWrapper<TJldzpcom> qw=new QueryWrapper<>();
            qw.eq("ph",ph);
            qw.eq("xh",xh);
            qw.eq("tp",tmd);
            qw.last("limit 1");
            return JsonResult.success(getOne(qw));
        }
        if(!StringUtils.instance.isEmpty(ph)){
            QueryWrapper<TJldzpcom> qw=new QueryWrapper<>();
            qw.eq("ph",ph);
            qw.eq("tp",tmd);
            qw.orderByDesc("id");
            return JsonResult.success(list(qw));

        }else{
//            如果没有批号，就按时间参数，如果没有时间参数，就返回最近的20条，如果只有dt0,就返回dt0之后的数据20条，如果只有dt1,就返回dt1之前的20条数据
            QueryWrapper<TJldzpcom> qw=new QueryWrapper<>();
            qw.eq("tp", tmd);
            qw.orderByDesc("id");
            if(dt0!=null && dt1!=null){
                qw.ge("date", dt0);//大于等于dt0
                qw.le("date1", dt1);//小于等于dt1
            }else if(dt0!=null){
                qw.ge("date", dt0);//大于等于dt0
                qw.last("limit 20");
            }else if(dt1!=null){
                qw.le("date1", dt1);//小于等于dt1
                qw.last("limit 20");
            }else {
                qw.last("limit 20");
            }
            return JsonResult.success(list(qw));
        }

    }

    //统计
    @Override
    public Object getTj(HttpServletRequest request,String ph,Integer gx,Integer tmd) {
        // 获取第一条时间
        QueryWrapper<TJldzpcom> firstTimeQw = new QueryWrapper<>();
        firstTimeQw.eq("ph", ph);
        firstTimeQw.eq("tp",tmd);
        firstTimeQw.orderByAsc("date").last("limit 1");
        TJldzpcom firstRecord = getOne(firstTimeQw);
        if (firstRecord == null) {
            return JsonResult.error(ErrorCode.ERR_200);
        }

        Date firstTime = firstRecord.getDate();

//        String tp = firstRecord.getTp();
        String bn = firstRecord.getBn();

        // 获取最后一条时间
        QueryWrapper<TJldzpcom> lastTimeQw = new QueryWrapper<>();
        lastTimeQw.eq("ph", ph);
        lastTimeQw.eq("tp",tmd);
        lastTimeQw.orderByDesc("date").last("limit 1");
        TJldzpcom lastRecord = getOne(lastTimeQw);
        Date lastTime = lastRecord.getDate();

        // 获取样品数
        QueryWrapper<TJldzpcom> countQw = new QueryWrapper<>();
        countQw.eq("ph", ph);
        countQw.eq("tp",tmd);
        int sampleCount = count(countQw);

        // 获取合格数
        QueryWrapper<TJldzpcom> qualifiedCountQw = new QueryWrapper<>();
        qualifiedCountQw.eq("ph", ph);
        qualifiedCountQw.eq("tp", tmd);
        qualifiedCountQw.eq("hege", "1");
        int qualifiedCount = count(qualifiedCountQw);

        QueryWrapper<TJldzpcom> qw = new QueryWrapper<>();
        qw.eq("ph", ph);
        qw.eq("tp", tmd);

// 构建 SELECT 子句，仅排除 NULL 值，保留 0 值参与统计
        String selectClause =
                "avg(case when v0 is not null then v0 end) v0, " +
                "avg(case when v1 is not null then v1 end) v1, " +
                "avg(case when v2 is not null then v2 end) v2, " +
                "avg(case when v3 is not null then v3 end) v3, " +
                "avg(case when v4 is not null then v4 end) v4, " +
                "avg(case when v5 is not null then v5 end) v5, " +
                "avg(case when v6 is not null then v6 end) v6, " +
                "avg(case when v7 is not null then v7 end) v7, " +
                "avg(case when v8 is not null then v8 end) v8, " +
                "avg(case when v9 is not null then v9 end) v9, " +
                "avg(case when v10 is not null then v10 end) v10, " +
                "avg(case when v11 is not null then v11 end) v11, " +
                "avg(case when v12 is not null then v12 end) v12, " +
                "avg(case when v13 is not null then v13 end) v13, " +
                "avg(case when v14 is not null then v14 end) v14, " +

                "STDDEV_POP(case when v0 is not null then v0 end) s0, " +
                "STDDEV_POP(case when v1 is not null then v1 end) s1, " +
                "STDDEV_POP(case when v2 is not null then v2 end) s2, " +
                "STDDEV_POP(case when v3 is not null then v3 end) s3, " +
                "STDDEV_POP(case when v4 is not null then v4 end) s4, " +
                "STDDEV_POP(case when v5 is not null then v5 end) s5, " +
                "STDDEV_POP(case when v6 is not null then v6 end) s6, " +
                "STDDEV_POP(case when v7 is not null then v7 end) s7, " +
                "STDDEV_POP(case when v8 is not null then v8 end) s8, " +
                "STDDEV_POP(case when v9 is not null then v9 end) s9, " +
                "STDDEV_POP(case when v10 is not null then v10 end) s10, " +
                "STDDEV_POP(case when v11 is not null then v11 end) s11, " +
                "STDDEV_POP(case when v12 is not null then v12 end) s12, " +
                "STDDEV_POP(case when v13 is not null then v13 end) s13, " +
                "STDDEV_POP(case when v14 is not null then v14 end) s14" ;

        qw.select(selectClause);

        Map<String, Object> resultMap = listMaps(qw).get(0);
        TTjdzpcom tjEnt = JSONObject.parseObject(JSONObject.toJSONString(resultMap), TTjdzpcom.class);
//        JSONObject result = new JSONObject(resultMap);
        tjEnt.setPh(ph);
        tjEnt.setTp(tmd+"");
        tjEnt.setBn(bn);
        tjEnt.setDt0(firstTime);
        tjEnt.setDt1(lastTime);
        tjEnt.setQt((double) sampleCount);
        tjEnt.setQqt((double) qualifiedCount);
        if(tmd==7){
            // 第一步：查询原始数据
            QueryWrapper<TJldzpcom> dataQw = new QueryWrapper<>();
            dataQw.select("v8, v9, v10, v11");
            dataQw.eq("ph", ph);
            dataQw.eq("tp", tmd);
            List<Map<String, Object>> dataList = listMaps(dataQw);

            // 第二步：在Java中计算
            List<Double> values = new ArrayList<>();
            for (Map<String, Object> row : dataList) {
                for (int i = 8; i <= 11; i++) {
                    Double val = (Double) row.get("v" + i);
                    if (val != null) {
                        values.add(val);
                    }
                }
            }

            // 计算平均值和标准差
            Double v12 = null;
            Double s12 = null;

            if (!values.isEmpty()) {
                // 计算平均值
                double avg = values.stream().mapToDouble(Double::doubleValue).average().orElse(0);
                v12 = avg;

                // 计算标准差
                if (values.size() > 1) {
                    double variance = values.stream()
                            .mapToDouble(v -> Math.pow(v - avg, 2))
                            .sum() / values.size();
                    s12 = Math.sqrt(variance);
                }
            }

            // 将结果放入resultMap
            tjEnt.setV(v12);
            tjEnt.setS(s12);
        }else if(tmd==5){
            // 第一步：查询原始数据
            QueryWrapper<TJldzpcom> dataQw = new QueryWrapper<>();
            dataQw.select("v10,v11,v12,v13,v14");
            dataQw.eq("ph", ph);
            dataQw.eq("tp", tmd);
            List<Map<String, Object>> dataList = listMaps(dataQw);

            // 第二步：在Java中计算
            List<Double> values = new ArrayList<>();
            for (Map<String, Object> row : dataList) {
                for (int i = 10; i <= 14; i++) {
                    Double val = (Double) row.get("v" + i);
                    if (val != null) {
                        values.add(val);
                    }
                }
            }

            // 计算平均值和标准差
            Double v12 = null;
            Double s12 = null;

            if (!values.isEmpty()) {
                // 计算平均值
                double avg = values.stream().mapToDouble(Double::doubleValue).average().orElse(0);
                v12 = avg;

                // 计算标准差
                if (values.size() > 1) {
                    double variance = values.stream()
                            .mapToDouble(v -> Math.pow(v - avg, 2))
                            .sum() / values.size();
                    s12 = Math.sqrt(variance);
                }
            }

            // 将结果放入resultMap
            tjEnt.setV(v12);
            tjEnt.setS(s12);
        }

        if(gx==0) {
            tjMapper.insert(tjEnt);
        }else{
            tjMapper.update(tjEnt,new QueryWrapper<TTjdzpcom>().eq("ph",ph));
        }
        return JsonResult.success(tjEnt);

//            result.put("avg", listMaps(qw).get(0));
        //根据tp类型为5或6时，不用统计数据，只把remark作为数组返回 其它情况，就返回第一条remark
//        if (tp.equals("5") || tp.equals("6")) {
//            QueryWrapper<TJldzpcom> remarkQw = new QueryWrapper<>();
//            remarkQw.eq("ph", ph);
//            remarkQw.select("remark");
//            List<TJldzpcom> list = list(remarkQw);
//            JSONArray remarkArray = new JSONArray();
//            for (TJldzpcom record : list) {
//                remarkArray.add(record.getRemark());
//            }
//            result.put("remark", remarkArray);
//        } else {
//            JSONArray remarkArray = new JSONArray();
//            remarkArray.add(firstRecord != null ? firstRecord.getRemark() : "");
//            result.put("remark", remarkArray);
//        }
//        return result;
    }




}
